CREATE TABLE ACTSTAT (
ACTSTATID            NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(100)
);

CREATE TABLE CENTERST (
CENTERSTID           NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(100)
);

CREATE TABLE CURENTST (
CURENTSTID           NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(100)
);

CREATE TABLE OPERSTAT (
OPERSTATID           NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(100)
);

CREATE TABLE SOCRBASE (
KOD_T_ST             NUMERIC(10),
"LEVEL"              NUMERIC(10)          NOT NULL,
SCNAME               VARCHAR(10)          NOT NULL,
SOCRNAME             VARCHAR(50)
);

CREATE TABLE ADDROBJ (
AOID                 VARCHAR(36)          NOT NULL,
FORMALNAME           VARCHAR(120),
REGIONCODE           VARCHAR(2),
AUTOCODE             CHAR(1),
AREACODE             VARCHAR(3),
CITYCODE             VARCHAR(3),
CTARCODE             VARCHAR(3),
PLACECODE            VARCHAR(3),
STREETCODE           VARCHAR(4),
EXTRCODE             VARCHAR(4),
SEXTCODE             VARCHAR(3),
OFFNAME              VARCHAR(120),
POSTALCODE           CHAR(6),
IFNSFL               VARCHAR(4),
TERRIFNSFL           VARCHAR(4),
IFNSUL               VARCHAR(4),
TERRIFNSUL           VARCHAR(4),
OKATO                VARCHAR(11),
OKTMO                VARCHAR(8),
UPDATEDATE           DATE,
SHORTNAME            VARCHAR(10),
PARENTGUID           VARCHAR(36),
AOGUID               VARCHAR(36),
CODE                 VARCHAR(17),
PLAINCODE            VARCHAR(15),
ACTSTATUS            NUMERIC(10),
CENTSTATUS           NUMERIC(10),
OPERSTATUS           NUMERIC(10),
CURRSTATUS           NUMERIC(10),
STARTDATE            DATE,
ENDDATE              DATE,
NORMDOC              VARCHAR(36),
AOLEVEL              NUMERIC(10),
LIVESTATUS           NUMERIC(1),
NEXTID               VARCHAR(36),
PREVID               VARCHAR(36),
FOREIGN KEY (ACTSTATUS)
      REFERENCES ACTSTAT (ACTSTATID),
FOREIGN KEY (CENTSTATUS)
      REFERENCES CENTERST (CENTERSTID),
FOREIGN KEY (CURRSTATUS)
      REFERENCES CURENTST (CURENTSTID),
FOREIGN KEY (OPERSTATUS)
      REFERENCES OPERSTAT (OPERSTATID),
FOREIGN KEY (AOLEVEL, SHORTNAME)
      REFERENCES SOCRBASE ("LEVEL", SCNAME)
);

CREATE  INDEX FK_ADDROBJ_AOGUID ON ADDROBJ (
AOGUID ASC
);

CREATE  INDEX FK_ADDROBJ_NEXTID ON ADDROBJ (
NEXTID ASC
);

CREATE  INDEX FK_ADDROBJ_PARENTGUID ON ADDROBJ (
PARENTGUID ASC
);

CREATE  INDEX FK_ADDROBJ_PREVID ON ADDROBJ (
PREVID ASC
);

CREATE  INDEX FK_ADDROBJ_REFERENCE_SOCRBASE ON ADDROBJ (
SHORTNAME ASC,
AOLEVEL ASC
);

CREATE  INDEX FK_ADDR_ACTSTAT ON ADDROBJ (
ACTSTATUS ASC
);

CREATE  INDEX FK_ADDR_CENTERST ON ADDROBJ (
CENTSTATUS ASC
);

CREATE  INDEX FK_ADDR_CURRENTS ON ADDROBJ (
CURRSTATUS ASC
);

CREATE  INDEX FK_ADDR_OPERSTAT ON ADDROBJ (
OPERSTATUS ASC
);

CREATE  INDEX FK_ADRR_NORDOC ON ADDROBJ (
NORMDOC ASC
);

CREATE TABLE ESTSTAT (
ESTSTATID            NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(20),
SHORTNAME            VARCHAR(20)
);

CREATE TABLE HSTSTAT (
HOUSESTID            NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(60)
);

CREATE TABLE STRSTAT (
STRSTATID            NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(20),
SHORTNAME            VARCHAR(20)
);

CREATE TABLE HOUSE (
HOUSEID              VARCHAR(36)          NOT NULL,
POSTALCODE           CHAR(6),
IFNSFL               VARCHAR(4),
TERRIFNSFL           VARCHAR(4),
IFNSUL               VARCHAR(4),
TERRIFNSUL           VARCHAR(4),
OKATO                VARCHAR(11),
OKTMO                VARCHAR(8),
UPDATEDATE           DATE,
HOUSENUM             VARCHAR(20),
ESTSTATUS            NUMERIC(10),
BUILDNUM             VARCHAR(10),
STRUCNUM             VARCHAR(10),
STRSTATUS            NUMERIC(10),
HOUSEGUID            VARCHAR(36),
AOGUID               VARCHAR(36),
STARTDATE            DATE,
ENDDATE              DATE,
STATSTATUS           NUMERIC(10),
NORMDOC              VARCHAR(36),
COUNTER              NUMERIC(10),
FOREIGN KEY (ESTSTATUS)
      REFERENCES ESTSTAT (ESTSTATID),
FOREIGN KEY (STATSTATUS)
      REFERENCES HSTSTAT (HOUSESTID),
FOREIGN KEY (STRSTATUS)
      REFERENCES STRSTAT (STRSTATID)
);

CREATE  INDEX FK_HOUSE_AOGUID ON HOUSE (
AOGUID ASC
);

CREATE  INDEX FK_HOUSE_ESTST ON HOUSE (
ESTSTATUS ASC
);

CREATE  INDEX FK_HOUSE_HOUSEGUID ON HOUSE (
HOUSEGUID ASC
);

CREATE  INDEX FK_HOUSE_NORMDOC ON HOUSE (
NORMDOC ASC
);

CREATE  INDEX FK_HOUSE_STATSTAT ON HOUSE (
STATSTATUS ASC
);

CREATE  INDEX FK_HOUSE_STRSTAT ON HOUSE (
STRSTATUS ASC
);

CREATE TABLE INTVSTAT (
INTVSTATID           NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(60)
);

CREATE TABLE HOUSEINT (
POSTALCODE           VARCHAR(6),
IFNSFL               VARCHAR(4),
TERRIFNSFL           VARCHAR(4),
IFNSUL               VARCHAR(4),
TERRIFNSUL           VARCHAR(4),
OKATO                VARCHAR(11),
OKTMO                VARCHAR(8),
UPDATEDATE           DATE,
INTSTART             NUMERIC(10),
INTEND               NUMERIC(10),
HOUSEINTID           VARCHAR(36)          NOT NULL,
INTGUID              VARCHAR(36),
AOGUID               VARCHAR(36),
STARTDATE            DATE,
ENDDATE              DATE,
INTSTATUS            NUMERIC(10),
NORMDOC              VARCHAR(36),
COUNTER              NUMERIC(10),
FOREIGN KEY (INTSTATUS)
      REFERENCES INTVSTAT (INTVSTATID)
);

CREATE  INDEX FK_HOUSEINT_AOGUID ON HOUSEINT (
AOGUID ASC
);

CREATE  INDEX FK_HOUSEINT_INTST ON HOUSEINT (
INTSTATUS ASC
);

CREATE  INDEX FK_HOUSEINT_NORDOC ON HOUSEINT (
NORMDOC ASC
);

CREATE TABLE LANDMARK (
LANDID               VARCHAR(36)          NOT NULL,
LOCATION             VARCHAR(500),
POSTALCODE           VARCHAR(6),
IFNSFL               VARCHAR(4),
TERRIFNSFL           VARCHAR(4),
IFNSUL               VARCHAR(4),
TERRIFNSUL           VARCHAR(4),
OKATO                VARCHAR(11),
OKTMO                VARCHAR(8),
UPDATEDATE           DATE,
LANDGUID             VARCHAR(36),
AOGUID               VARCHAR(36),
STARTDATE            DATE,
ENDDATE              DATE,
NORMDOC              VARCHAR(36)
);

CREATE  INDEX FK_LANDMARK_LANDGUID ON LANDMARK (
LANDGUID ASC
);

CREATE  INDEX FK_LANDMARK_NORDOC ON LANDMARK (
NORMDOC ASC
);

CREATE  INDEX FL_LANDMARK_AOGUID ON LANDMARK (
AOGUID ASC
);

CREATE TABLE NDOCTYPE (
NDTYPEID             NUMERIC(10)          NOT NULL,
NAME                 VARCHAR(100)
);

CREATE TABLE NORDOC (
NORMDOCID            VARCHAR(36)          NOT NULL,
DOCNAME              VARCHAR(1000),
DOCDATE              DATE,
DOCNUM               VARCHAR(20),
DOCTYPE              NUMERIC(10),
DOCIMGID             NUMERIC(10),
FOREIGN KEY (DOCTYPE)
      REFERENCES NDOCTYPE (NDTYPEID)
);

CREATE  INDEX FK_DOC_TYPE ON NORDOC (
DOCTYPE ASC
);

